﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_ImportOrderClearance : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection myConnection2;

        public panel_ImportOrderClearance()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.myConnection2 = new SqlConnection(mySetting.SQLConnectionString);

            LoadData();

            this.OrderClearance.DoubleClick += OrderClearance_DoubleClick;
            this.OrderClearance.PopupMenuShowing += OrderClearance_PopupMenuShowing;
            this.orderClearanceContext.ItemClicked += orderClearanceContext_ItemClicked;
        }

        void OrderClearance_DoubleClick(object sender, EventArgs e)
        {
            callEditForm();
        }

        void orderClearanceContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.orderClearanceContext.Hide();
            DataRow row = OrderClearance.GetFocusedDataRow();
            switch (e.ClickedItem.Name)
            {
                case "importSelection":
                    importDatabase();
                    break;
                case "editSelection":
                    callEditForm();                    
                    break;
                case "addSelection":
                    frm_Add_ImportOrderClearance add_Form = new frm_Add_ImportOrderClearance();                    
                    add_Form.ShowDialog();
                    LoadData();
                    break;
                case "deleteSelection":
                    deleteDatabase();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void OrderClearance_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.orderClearanceContext.Show(MousePosition);
        }

        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT t1.[ID], t1.[ClearanceNo], t2.[RegisterDate], t2.[FinishedDate], t1.[OrderNo],"
                + " t1.[PartNo], t1.[Quanity], t1.[IQID], t1.[BuyingUnit], t1.[IQUnit], t1.[Buying/IQEx]"
                + " FROM [241_ImportOrderClearance] AS t1"
                + " INNER JOIN [241_CustomClearance] AS t2 ON t1.[ClearanceNo]=t2.[ID]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                foreach (DataRow part_row in sourceDataSet.Tables[0].Rows)
                {
                    if (!string.IsNullOrEmpty(part_row["BuyingUnit"].ToString().Trim()))
                    {
                        if (((int)(part_row["BuyingUnit"].ToString().Trim()[0])) == 65402)
                        {
                            part_row.BeginEdit();
                            part_row["BuyingUnit"] = "PC";
                            part_row.EndEdit();
                        }
                    }

                    if (!string.IsNullOrEmpty(part_row["IQUnit"].ToString().Trim()))
                    {
                        if (((int)(part_row["IQUnit"].ToString().Trim()[0])) == 65402)
                        {
                            part_row.BeginEdit();
                            part_row["IQUnit"] = "PC";
                            part_row.EndEdit();
                        }
                    }
                }
                gridOrderClerance.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                MessageBox.Show("There is no data for Order-Clearance");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("ClearanceNo", typeof(string));                
                newTable.Columns.Add("RegisterDate", typeof(DateTime));
                newTable.Columns.Add("FinishedDate", typeof(DateTime));
                newTable.Columns.Add("OrderNo", typeof(string));
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("Quanity", typeof(float));
                newTable.Columns.Add("IQID", typeof(string));
                newTable.Columns.Add("BuyingUnit", typeof(string));                
                newTable.Columns.Add("IQUnit", typeof(string));
                newTable.Columns.Add("Buying/IQEx", typeof(float));
                gridOrderClerance.DataSource = newTable;
            }
            OrderClearance.Columns[0].Caption = "ID";
            OrderClearance.Columns[0].Visible = false;
            OrderClearance.Columns[1].Caption = "Clearance No.";            
            OrderClearance.Columns[2].Caption = "Register Date";
            OrderClearance.Columns[3].Caption = "Finished Date";
            OrderClearance.Columns[4].Caption = "Order No.";
            OrderClearance.Columns[5].Caption = "Part No.";
            OrderClearance.Columns[6].Caption = "Quantity No.";
            OrderClearance.Columns[7].Caption = "IQ ID";
            OrderClearance.Columns[8].Caption = "Buying Unit";            
            OrderClearance.Columns[9].Caption = "IQ Unit";
            OrderClearance.Columns[10].Caption = "Buying/IQ Exchange";
            OrderClearance.BestFitColumns();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_ImportOrderClearance add_Form = new frm_Add_ImportOrderClearance();
            add_Form.ShowDialog();
            LoadData();
        }

        private void buttonEdit_Click(object sender, EventArgs e)
        {
            callEditForm();
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteDatabase();
        }

        //////////////////////////////////////////
        /// Delete database
        //////////////////////////////////////////
        private void deleteDatabase()
        {
            int[] rows = OrderClearance.GetSelectedRows();
            if (rows.Length <= 0)
            {
                MessageBox.Show("There is no selected record to delete");
            }
            else
            {
                if (MessageBox.Show("Are you really to delete selected records now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    string[] id_list = new string[rows.Length];
                    for (int i = 0; i < rows.Length; i++)
                    {
                        DataRow selected_row = OrderClearance.GetDataRow(rows[i]);
                        id_list[i] = "'" + selected_row[0].ToString() + "'";
                    }
                    string id = string.Join(",", id_list);                    
                    try
                    {
                        myConnection.Open();
                        string sql = "DELETE [241_ImportOrderClearance] WHERE ID IN (" + id + ")";
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();                        
                        myConnection.Close();

                        MessageBox.Show("Order-Clearance deleted success.");
                        LoadData();
                    }
                    catch //(Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        //MessageBox.Show(ex.Message);
                        MessageBox.Show("Cannot delete Comp. Please try again.");
                    }
                }
            }
        }       

        private void callEditForm()
        {            
            /*
            DataRow row = OrderClearance.GetFocusedDataRow();
            frm_Edit_ImportOrderClearance editForm = new frm_Edit_ImportOrderClearance();
            editForm.id = row[0].ToString();
            if (editForm.LoadData())
            {
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                MessageBox.Show("Cannot load data for selected record");
            } */
        }

        ////////////////////////////////////
        /// Update database From Excel
        ////////////////////////////////////
        private void importDatabase()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();

            bool have_data = false;

            importFile.Filter = "Excel 2007-2013 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [ClearanceNo], [OrderNo]"
                        + " FROM [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }

            if (have_data)
            {

                DataSet order_clearance = new DataSet();
                string sql = null;
                SqlDataAdapter adapter;
                DataTable orderclerance_local;
                try
                {
                    //Get Data From Local Database
                    myConnection.Open();
                    sql = "SELECT * FROM [241_OrderClearanceTemp]";
                    SqlCommand cmd = new SqlCommand(sql, myConnection);
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(order_clearance, "241_OrderClearanceTemp");
                    orderclerance_local = order_clearance.Tables["241_OrderClearanceTemp"];
                    int add = 0;
                    foreach (DataRow excel_row in excel_data.Rows)
                    {
                        DataRow[] current_row = orderclerance_local.Select("ClearanceNo='" + excel_row["ClearanceNo"].ToString().Trim() + "'"
                            + " AND OrderNo='" + excel_row["OrderNo"].ToString().Trim() + "'");
                        if (current_row.Length <= 0)
                        {
                            if (ExistClearance(excel_row["ClearanceNo"].ToString().Trim()))
                            {
                                DataRow new_row = orderclerance_local.NewRow();
                                new_row["ClearanceNo"] = excel_row["ClearanceNo"].ToString().Trim();
                                new_row["OrderNo"] = excel_row["OrderNo"].ToString().Trim();
                                orderclerance_local.Rows.Add(new_row);
                                add++;
                            }
                        }
                    }


                    SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                    //Update to Local Database
                    adapter.Update(order_clearance, "241_OrderClearanceTemp");

                    adapter.Dispose();
                    cmd.Dispose();

                    cmd = myConnection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [241_ImportOrderClearance] ([ClearanceNo], [OrderNo], [PartNo],"
                        + " [Quanity], [IQID], [BuyingUnit], [IQUnit], [Buying/IQEx])"
                        + " SELECT t1.ClearanceNo, t1.OrderNo, LTRIM(RTRIM(t2.[PartNo])) AS PartNo,"
                        + " (t2.[Quantity] - ISNULL(t3.Quanity, 0)) AS Quantity, t2.[IQID], t2.[BuyingUnit], t2.[IQUnit],"
                        + " t2.[Buying/IQEx] FROM [241_OrderClearanceTemp] AS t1 LEFT OUTER JOIN"
                        + " [VPartReceiveUnion] AS t2 ON t1.OrderNo COLLATE Japanese_Unicode_CI_AI=t2.OrderNo LEFT OUTER JOIN"
                        + " [241_ImportOrderClearance] AS t3 ON t3.OrderNo=t1.OrderNo"
                        + " WHERE  (t2.[Quantity] - ISNULL(t3.Quanity, 0)) > 0";
                    cmd.CommandTimeout = 30000;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    cmd = myConnection.CreateCommand();
                    cmd.CommandText = "DELETE FROM [241_OrderClearanceTemp]";                    
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    myConnection.Close();                   

                    LoadData();

                    MessageBox.Show("Database has been updated successfull.\r\n " + add + " records has been added");

                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot connect to AS400");
                }
            }
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }

        /////////////////////////////////////////////////
        /// Check exist clearance
        ////////////////////////////////////////////////
        private bool ExistClearance(string clearance)
        {
            bool ret = false;
            try
            {
                myConnection2.Open();
                SqlCommand sql_cmd = myConnection2.CreateCommand();
                sql_cmd.CommandText = "SELECT COUNT(ID) FROM [241_CustomClearance] WHERE ID='" + clearance + "'";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    if (dataReader[0] != DBNull.Value)
                    {
                        if (Int32.Parse(dataReader[0].ToString()) > 0)
                        {
                            ret = true;
                        }
                    }
                }
            }
            catch
            {
                if (myConnection2.State == ConnectionState.Open)
                {
                    myConnection2.Close();
                }
                ret = true;
            }
            return ret;
        }
    }
}
